package del2;

import java.sql.*;

public class DbDataAccessObject {

	static final String url = "jdbc:derby:PersonDB;create=true";
	static final String driver = "org.apache.derby.jdbc.EmbeddedDriver";

	public static void savePerson(Person person) throws SQLException {
		Connection con = null;
		Statement stmt = null;
		
		try {
			Class.forName(driver);
			con = DriverManager.getConnection(url);
			stmt = con.createStatement();
		
			String sql = "INSERT INTO Person (person_pk, First_Name, Last_Name)";
			sql += "VALUES (" + person.getId() + ", '" + person.getFirstName() + "', '" + person.getLastName() + "')";
			stmt.executeUpdate(sql);
			
		} catch (SQLException e) {
			System.out.println("Caught: " + e);
		} catch (ClassNotFoundException e) {
			System.out.println("Caught: " + e);
		} finally {
			if(stmt != null) stmt.close();
			if(con != null) con.close();
		}
	}

	public static Person loadPerson(int id) throws SQLException {
		Connection con = null;
		Statement stmt = null;
		
		try {
			Class.forName(driver);
			con = DriverManager.getConnection(url);
			stmt = con.createStatement();
		
			String sql = "SELECT * FROM Person WHERE person_pk = " + id;
			ResultSet res = stmt.executeQuery(sql);
			// To avoid Invalid cursor state
			res.next();
			
			Person person = new Person(res.getInt("PERSON_PK"), res.getString("FIRST_NAME"), res.getString("LAST_NAME"));
			
			return person;	
			
		} catch (SQLException e) {
			System.out.println("Caught: " + e);
		} catch (ClassNotFoundException e) {
			System.out.println("Caught: " + e);
		}finally {
			if(stmt != null) stmt.close();
			if(con != null) con.close();
		}
		return null;
	}
}